В целях корректировки рекламного бюджета проводится когортный анализ клиентов сервиса, расчет маркетинговых, продуктовых и метрик электронной коммерции в разрезе когорт
Исследование проводится на основании данных Яндекс.Афиши с июня 2017 по конец мая 2018 года (dataset: лог сервера с данными о посещениях сайта Яндекс.Афиши; заказы за этот период; статистика рекламных расходов).
1. Загрузка и предобработка данных
1.1. Данные о визитах на сайт/сервис
1.2. Данные о заказах
1.3. Данные о рекламных расходах
2. Расчет метрик
2.1. Продуктовые метрики (на основании данных визитов)
2.1.1. DAU, WAU, MAU и их изменение во времениих
2.1.1.1. DAU
2.1.1.2. WAU
2.1.1.3. MAU
2.1.1.4. Графики изменение DAU, WAU и MAU во времени
Выводы по 2.1.1.
2.1.2. Количество заходов пользователей за день на сайт (среднее и изменение во времени)
2.1.2.1. На основании метрики DAU
2.1.2.2. Анализ на основании MAU
2.1.2.3. Анализ за весь период
Выводы по 2.1.2.
2.1.3. Продолжительность сессии пользователей на сайте (ASL и распределение по пользователям)
Выводы по 2.1.3.
2.1.4. Рассчет Retention Rate
Выводы по 2.1.4.
2.2. Метрики электронной коммерции (на основании данных заказов)
2.2.1. Исследование среднего времени с момента первого посещения сайта до покупки
Выводы по 2.2.1.
2.2.2. Расчет среднего количества покупок на одного клиента за период
Выводы по 2.2.2.
2.2.3. Расчет средней выручки с пользователя (изучение динамики метрики во времени)
2.2.3.1. Расчет в рамках когорт
2.2.3.2. Без деления на когорты
Выводы по 2.2.3.
2.2.4. Анализ накопительного LTV по когортам во времени
Выводы по 2.2.4.
2.3. Маркетинговые метрики
2.3.1. Общая сумма расходов на маркетинг, распределение расходов по источникам, изменение распределения во времени
Выводы по 2.3.1.
2.3.2. CAC
Выводы по 2.3.2.
2.3.3. ROMI по когортам в разрезе источников
Выводы по 2.3.3.
2.3.4. Анализ заказов пользователей в разрезе устройств и платформ
Общие выводы по исследованию
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')
df_v = pd.read_csv('visits_log.csv')
df_o = pd.read_csv('orders_log.csv')
df_c = pd.read_csv('costs.csv')
df_v.head()
df_v.info()
# определение уникальных типов устройств
device_list = df_v['Device'].unique().tolist()
device_list
# проверка наличия полных дубликатов строк
df_v.duplicated().sum()
End Ts и Start Ts к типу datetime.df_v.columns =[col.lower().replace(' ', '_') for col in df_v.columns.to_list()]
df_v['end_ts'] = pd.to_datetime(df_v['end_ts'])
df_v['start_ts'] = pd.to_datetime(df_v['start_ts'])
df_v.info()
# проверка диапазона дат (01.06.2017 - 31.05.2018 гг)
df_v['start_ts'].describe()
df_o.head()
df_o.info()
# проверка наличия полных дубликатов строк
df_o.duplicated().sum()
Buy Ts к типу datetime.df_o.columns =[col.lower().replace(' ', '_') for col in df_o.columns.to_list()]
df_o['buy_ts'] = pd.to_datetime(df_o['buy_ts'])
df_o.info()
# проверка диапазона дат (01.06.2017 - 31.05.2018 гг)
df_o['buy_ts'].describe()
# исключение строк с датой больше или равно 2018-06-01 00:00:00
df_o = df_o[df_o['buy_ts'] < '2018-06-01 00:00:00']
df_o['buy_ts'].describe()
df_c.head()
df_c.info()
df_c.duplicated().sum()
dt к типу datetime.df_c['dt'] = pd.to_datetime(df_c['dt'])
df_c.info()
# проверка диапазона дат (01.06.2017 - 31.05.2018 гг)
df_c['dt'].describe()
df_v.head(1)
# добавление столбцов, соответвующих году, месяцу, недели, дню начала сессии пользователя
df_v['ses_m'] = df_v['start_ts'].dt.strftime('%Y-%m')
df_v['ses_w'] = df_v['start_ts'].dt.strftime('%y%W')
df_v['ses_d'] = df_v['start_ts'].dt.date
df_v['ses_m_dt'] = df_v['start_ts'].astype('datetime64[M]')
df_v.head()
dau_all = df_v.groupby('ses_d').agg({'uid': ['count','nunique']}).reset_index()
dau_touch = df_v[df_v['device'] == 'touch'].groupby('ses_d').agg({'uid': ['count','nunique']}).reset_index()
dau_desktop = df_v[df_v['device'] == 'desktop'].groupby('ses_d').agg({'uid': ['count','nunique']}).reset_index()
dau_all.columns = ['ses_d', 'count_ses','count_un_users']
dau_touch.columns = ['ses_d', 'count_ses','count_un_users']
dau_desktop.columns = ['ses_d', 'count_ses','count_un_users']
dau_all['count_un_users'].hist()
plt.title('Гистограмма значений DAU')
plt.ylabel('Частотность значения, шт')
plt.xlabel('Количество уникальных пользователей в день, шт.')
plt.show()
dau_all['count_un_users'].describe()
# среднее значение и медиана имеют сопоставимые значения - в качестве "среднего" будем использовать среднее значение
dau_mean = int(round(dau_all['count_un_users'].mean(), 0))
dau_mean
wau_all = df_v.groupby('ses_w').agg({'uid': 'nunique'}).reset_index()
wau_touch = df_v[df_v['device'] == 'touch'].groupby('ses_w').agg({'uid': 'nunique'}).reset_index()
wau_desktop = df_v[df_v['device'] == 'desktop'].groupby('ses_w').agg({'uid': 'nunique'}).reset_index()
wau_touch.columns = ['ses_w','count_un_users']
wau_all.columns = ['ses_w','count_un_users']
wau_desktop.columns = ['ses_w','count_un_users']
wau_all['count_un_users'].hist()
plt.title('Гистограмма значений WAU')
plt.ylabel('Частотность значения, шт')
plt.xlabel('Количество уникальных пользователей в неделю, шт.')
plt.show()
wau_all['count_un_users'].describe()
# среднее значение и медиана имеют сопоставимые значения - в качестве "среднего" будем использовать среднее значение
wau_mean = int(round(wau_all['count_un_users'].mean(), 0))
wau_mean
mau_all = df_v.groupby('ses_m').agg({'uid': ['count','nunique']}).reset_index()
mau_touch = df_v[df_v['device'] == 'touch'].groupby('ses_m').agg({'uid': ['count','nunique']}).reset_index()
mau_desktop = df_v[df_v['device'] == 'desktop'].groupby('ses_m').agg({'uid': ['count','nunique']}).reset_index()
mau_all.columns = ['ses_m', 'count_ses', 'count_un_users']
mau_touch.columns = ['ses_m', 'count_ses', 'count_un_users']
mau_desktop.columns = ['ses_m', 'count_ses', 'count_un_users']
mau_all['count_un_users'].hist()
plt.title('Гистограмма значений MAU')
plt.ylabel('Частотность значения, шт')
plt.xlabel('Количество уникальных пользователей в месяц, шт.')
plt.show()
mau_all['count_un_users'].describe()
# среднее значение и медиана имеют сопоставимые значения - в качестве "среднего" будем использовать среднее значение
mau_mean = int(round(mau_all['count_un_users'].mean(), 0))
mau_mean
plt.figure(figsize=(12,4), dpi=200)
sns.lineplot(x='ses_d', y='count_un_users', data=dau_all, label='DAU все устройства')
sns.lineplot(x='ses_d', y='count_un_users', data=dau_desktop, label='DAU desktop')
sns.lineplot(x='ses_d', y='count_un_users', data=dau_touch, label='DAU touch')
plt.title('График изменения DAU по времени')
plt.xlabel('Дни')
plt.ylabel('Кол. уник. пользоват. в день, шт.')
plt.xticks(ticks=mau_all['ses_m'], rotation=45)
plt.show()
plt.figure(figsize=(12,4), dpi=200)
sns.lineplot(x='ses_w', y='count_un_users', data=wau_all, label='WAU все устройства')
sns.lineplot(x='ses_w', y='count_un_users', data=wau_desktop, label='WAU desktop')
sns.lineplot(x='ses_w', y='count_un_users', data=wau_touch, label='WAU touch')
plt.title('График изменения WAU по времени')
plt.xlabel('Недели')
plt.ylabel('Кол. уник. пользоват. в неделю, шт.')
plt.xticks( rotation=90)
plt.show()
device_list
plt.figure(figsize=(12,4), dpi=200)
sns.lineplot(x='ses_m', y='count_un_users', data=mau_all, label='MAU все устройства')
sns.lineplot(x='ses_m', y='count_un_users', data=mau_desktop, label='MAU desktop')
sns.lineplot(x='ses_m', y='count_un_users', data=mau_touch, label='MAU touch')
plt.title('График изменемния MAU по времени')
plt.xlabel('Месяцы')
plt.ylabel('Кол. уник. пользоват. в день, шт.')
plt.xticks(ticks=mau_all['ses_m'], rotation=45)
plt.show()
Cредние значения метрик по всем платформам за весь период (01.06.2017 - 31.05.2018 гг):
DAU = 908
WAU = 5_716
MAU = 23_228
Средние значения метрик не соответствуют математическому произведению одних из метрик на соответсвующие константы (количество дней в месяце, количество недель в месяце), что ожидаемо и не является аномалией.
График MAU показывает наличие сезонности в использованинии услуги пользователями: летом активность минимальна, с увеличением в осенние месяцы и пиков в ноябре, затем наблюдается снижение активности (однако представлый период данных не позволяет проанализировать сезонность "год к году"). При этом, на платформе touch сезонность в осенне-весенний период отсутствут, по сравнению с сезонностью decktop пользователей.
Таким образом, наблюдается различия в между платформами пользователей:
dau_all.head()
# добавление столбца с количеством заходов на сайт пользователей
dau_all['cnt_ses_per_user_d'] = round(dau_all['count_ses'] / dau_all['count_un_users'], 2)
dau_all.head(10)
dau_all['cnt_ses_per_user_d'].hist()
plt.title('Гистограмма заходов пользователей на сайт в день')
plt.ylabel('Частотность значения, шт')
plt.xlabel('Среднее количество заходов на сайт одного пользователя, шт.')
plt.show()
dau_all['cnt_ses_per_user_d'].describe()
cnt_ses_per_user_d_mean = round(dau_all['cnt_ses_per_user_d'].mean(), 2)
print(cnt_ses_per_user_d_mean)
plt.figure(figsize=(12,4), dpi=200)
sns.lineplot(x='ses_d', y='cnt_ses_per_user_d', data=dau_all, label='Количество сессий на пользователя в день')
plt.title('График изменения количества сессий на пользователя в день')
plt.xlabel('Дни')
plt.ylabel('Кол. сессий на пользователя, шт.')
plt.xticks(ticks=mau_all['ses_m'], rotation=45)
plt.axhline(cnt_ses_per_user_d_mean, ls='--', c='r', label='Среднее кол.сессий на польз. в день за период')
plt.legend()
plt.show()
mau_all['cnt_ses_per_user_m'] = round(mau_all['count_ses'] / mau_all['count_un_users'], 2)
mau_all.head()
# гистограмма распределения значений
mau_all['cnt_ses_per_user_m'].hist()
plt.title('Гистограмма заходов пользователей на сайт в месяц')
plt.ylabel('Частотность значения, шт')
plt.xlabel('Среднее количество заходов на сайт одного пользователя, шт.')
plt.show()
# расчет среднего значения сессий на одного пользователя за месяц
cnt_ses_per_user_m_mean = round(mau_all['cnt_ses_per_user_m'].mean(), 2)
cnt_ses_per_user_m_mean
# подсчет количества сессий и уникальных пользователей за весь период
pau_all = df_v.agg({'uid': ['count', 'nunique']}).T.reset_index(drop=True)
pau_all
# определение количества месяцев в рассматриваемом периоде
pau_all['count_month'] = len(mau_all)
pau_all
# расчет среднего значения сессий на одного пользователя за весь период
pau_all['cnt_ses_per_user_y'] = round(pau_all['count'] / pau_all['nunique'], 2)
pau_all
В среднем пользователи заходят на сайт 1.08 раз в день. Т.е. пользователь в среднем заходит на сайт только 1 раз в день.
Данный показатель достаточно стабилен на протежении всего периода исследования - наблюдаются небольшие отклонения в ту или другую сторону и есть один всплеск в конце ноября и одно падение показателя в марте-апреле (рассмотрение причин падения в данном исследовании не производится).
При этом, дополнительные исследования показали , что в течении месяца пользователь заходит на сайт только 1.28 раз, а за весь исследуемый период пользователи в среднем заходили только 1.58 раз.
Таким образом, трафик заходов на сайт обеспечивается в основном за счет новых пользователей (более подробно когортный анализ будет рассмотрен в следующих разделах исследования)
df_v['ses_duration_sec'] = (df_v['end_ts'] - df_v['start_ts']).dt.seconds
df_v.head()
ses_duration_sec_mean = int(round(df_v['ses_duration_sec'].mean(), 0))
ses_duration_sec_mean
df_v['ses_duration_sec'].describe()
plt.figure(figsize=(12,4), dpi=200)
sns.distplot(df_v['ses_duration_sec'], bins=100, kde=False)
plt.title('Гистограмма всех сессий пользователей за весь период')
plt.xlabel('Длительность сессии пользователя, сек')
plt.ylabel('Частотность значения, шт.')
plt.show()
# ящик с усами распределения длительности сессий
df_v[['ses_duration_sec']].boxplot()
plt.title('Boxplot (ящик с усами) длительности сессий пользователей')
plt.ylabel('Секунд')
plt.show()
border_value = 1.5*(df_v['ses_duration_sec'].quantile(q=0.75) - df_v['ses_duration_sec'].quantile(q=0.25))
border_value
df_v[['ses_duration_sec']].boxplot()
plt.ylim(0, border_value)
plt.title('Boxplot (ящик с усами) длительности сессий пользователей')
plt.ylabel('Секунд')
plt.show()
plt.figure(figsize=(12,4), dpi=200)
sns.distplot(df_v['ses_duration_sec'],bins=3000, kde=False)
plt.title('Гистограмма всех сессий пользователей за весь период с ограничением по верхнему усу')
plt.xlim(0, border_value)
plt.ylabel('Частотность значения, шт')
plt.xlabel('Длительность сессии, сек.')
plt.show()
# расчитаем моду распределения значений
print(df_v['ses_duration_sec'].mode())
Среднее значение mean() равно 644 сек и смещенно в право за счет длинного хвоста выбросов.
Мода равна 60 сек.
Характер распределения гистограммы продолжительности сессий пользователей показывает, что в качестве "среднего" значения лучше использовать медиану - 300 сек. (5 мин.), как значение отражающее распределение данных.
Таким образом, можно сделать вывод, что пользователи в среднем проводят на сайте около 5 минут.
# создание справочника с датой первой сессии пользователя в формате день, неделя, месяц
first_ses_date_by_users = df_v.groupby('uid').agg({'start_ts': 'min', })
first_ses_date_by_users.columns = ['first_ses_ts']
first_ses_date_by_users['first_ses_w'] = first_ses_date_by_users['first_ses_ts'].dt.strftime('%y%W').astype('int')
first_ses_date_by_users['first_ses_m'] = first_ses_date_by_users['first_ses_ts'].astype('datetime64[M]')
first_ses_date_by_users['first_ses_d'] = first_ses_date_by_users['first_ses_ts'].astype('datetime64[D]')
first_ses_date_by_users.sample(10)
# добавление в исходные данные данных о первой сессии
df_v = df_v.join(first_ses_date_by_users, on='uid')
df_v.sample(5)
# приведение поля ses_d к типу datetime (для вычисления lifetime когорты)
df_v['ses_d'] = df_v['ses_d'].astype('datetime64[D]')
df_v['lifetime'] = round((df_v['ses_m_dt'] - df_v['first_ses_m']) / np.timedelta64(1, 'M'), 0).astype(int)
df_v.sample(5)
count_users_cohort_gr = df_v.groupby(['first_ses_m', 'lifetime']).agg({'uid': 'nunique'})
count_users_cohort_gr.columns = ['count_u_users']
count_users_cohort_gr = count_users_cohort_gr.reset_index()
count_users_cohort_gr.head()
# создаем справочник по когортам - первоначальное количество уникальных пользователей
cohort_usres_cnt = count_users_cohort_gr[count_users_cohort_gr['lifetime'] == 0][['first_ses_m', 'count_u_users']]
cohort_usres_cnt.columns = ['first_ses_m', 'first_count_u_users']
cohort_usres_cnt.head()
# соединяем две таблицы
count_users_cohort_gr = count_users_cohort_gr.merge(cohort_usres_cnt, on='first_ses_m')
count_users_cohort_gr.head()
# в новом столбце расчитаем retention rate
count_users_cohort_gr['retention_rate'] = round(count_users_cohort_gr['count_u_users']
/ count_users_cohort_gr['first_count_u_users'], 4)
count_users_cohort_gr.head()
ret_rate_pvt = count_users_cohort_gr.pivot_table(index='first_ses_m',
columns='lifetime',
values='retention_rate',
aggfunc='sum')
ret_rate_pvt.index = ret_rate_pvt.index.strftime('%Y-%m-%d')
plt.figure(figsize=(12,4), dpi=200)
sns.heatmap(ret_rate_pvt, vmax=0.1, annot=True, fmt='.2%', linewidths=1, linecolor='gray')
plt.title('Таблица Retention Rate помесячных когорт в виде тепловой карты')
plt.ylabel('Когорта ')
plt.xlabel('Время жизни когорты, мес.')
plt.show()
# создание справочника дат первых заказов пользоватлей (uid)
uid_first_order_ts = df_o.groupby('uid').agg({'buy_ts': 'min'})
uid_first_order_ts = uid_first_order_ts.rename(columns= {'buy_ts': 'first_buy_ts'}).reset_index()
uid_first_order_ts.head()
len(uid_first_order_ts)
# соединение полученного справочника с данными о посещении сайта пользователем
lag_v_o = (df_v[df_v['start_ts'] == df_v['first_ses_ts']]
[['device', 'source_id', 'first_ses_ts', 'uid']]
.merge(uid_first_order_ts, how='right', on='uid'))
lag_v_o.head(5)
lag_v_o['first_ses_ts'].isna().sum()
Все строки заказов были сопоставлены с временем начала сессии в таблице сессий.
# добавление в итоговую таблицу столбца с длительностью между первым посящением и покупкой (в часах)
lag_v_o['lag_view_order_h'] = (lag_v_o['first_buy_ts'] - lag_v_o['first_ses_ts']) / np.timedelta64(1, 'h')
lag_v_o.sample(10)
lag_v_o['lag_view_order_h'].describe()
# данные содержат длинный хвост, который на три порядка смещает среднее значения вправо
# рассчитаем верхний ус
border_value_lag = round(1.5*(lag_v_o['lag_view_order_h'].quantile(q=0.75) - lag_v_o['lag_view_order_h'].quantile(q=0.25)), 2)
border_value_lag
# построим ящик с усами с ограничением по верхнему усу
lag_v_o[['lag_view_order_h']].boxplot()
plt.title('Boxplot (ящик с усами) длительности между первым посещением и заказом')
plt.ylabel('Часов')
plt.ylim(0, border_value_lag)
plt.show()
# учитывая такое расспределение данных для построения гистограммы ограничим длительность между посещением и заказом до 2ч.
plt.figure(figsize=(12,4), dpi=200)
sns.distplot(lag_v_o[lag_v_o['lag_view_order_h'].notna() & (lag_v_o['lag_view_order_h'] < 2)]['lag_view_order_h'],
kde=False)
plt.title('Гистограмма (не полного диапазона значений) времени между первым посещением сайта и покупкой')
plt.ylabel('Частотность значения, шт')
plt.xlabel('Длительность, часов')
plt.show()
Таким образом, между первым посещение сайта и заказом в среднем проходит около 30 минут.
# добавим в данные о заказах столбец формата месяц (datetime)
df_o['buy_m'] = df_o['buy_ts'].astype('datetime64[M]')
# добавим в справочник uid_first_order_ts столбец формата месяц (datetime)
uid_first_order_ts['first_buy_m'] = uid_first_order_ts['first_buy_ts'].astype('datetime64[M]')
uid_first_order_ts.head(2)
# добавим данный справочник к данным о заказах df_o
df_o = df_o.merge(uid_first_order_ts, on='uid')
df_o.head()
# добавим в df_o столбец с lifetime когорты (период когор месяц)
df_o['lifetime'] = ((df_o['buy_m'] - df_o['first_buy_m']) / np.timedelta64(1, 'M')).round().astype('int')
df_o.sample(5)
# сгруппируем данные по когортам и посчитаем сумму заказов, количество заказов, кол. уникальных пользователей
cohort_o_gr_m = df_o.groupby(['first_buy_m', 'lifetime']).agg({'revenue': 'sum', 'uid': ['count', 'nunique']}).reset_index()
cohort_o_gr_m.columns = ['first_buy_m', 'lifetime', 'sum_buy', 'count_buy', 'count_u_users']
cohort_o_gr_m
# создадим справочник с количеством уникальных пользователей в каждой когорте
cohort_o_count_users = cohort_o_gr_m[cohort_o_gr_m['lifetime'] == 0][['first_buy_m', 'count_u_users']]
cohort_o_count_users = cohort_o_count_users.rename(columns={'count_u_users': 'first_count_users'})
cohort_o_count_users
# добавим созданный справочник в cohort_o_gr_m
cohort_o_gr_m = cohort_o_gr_m.merge(cohort_o_count_users, on='first_buy_m')
cohort_o_gr_m.head(5)
# добавим столбец с количеством заказов на одного пользователя в когорте по lifetime
cohort_o_gr_m['count_buy_per_one_user'] = cohort_o_gr_m['count_buy'] / cohort_o_gr_m['first_count_users']
# добавим столбец с выручкой на одного пользователя в когорте по lifetime
cohort_o_gr_m['sum_buy_per_one_user'] = cohort_o_gr_m['sum_buy'] / cohort_o_gr_m['first_count_users']
cohort_o_gr_m
# построим сводную таблицу по когортам и по lifetime со значением количества заказов на одного пользователя
cohort_cnt_o_m = cohort_o_gr_m.pivot_table(index='first_buy_m', columns='lifetime', values= 'count_buy_per_one_user').round(2)
cohort_cnt_o_m.index = cohort_cnt_o_m.index.strftime('%Y-%m-%d')
plt.figure(figsize=(12,4), dpi=200)
sns.heatmap(cohort_cnt_o_m, vmax=0.15, annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('Таблица значений количества заказов на одного пользователя по когортам во времени')
plt.ylabel('Когорта ')
plt.xlabel('Время жизни когорты, мес.')
plt.show()
# сделаем срез данных ограничив время жизни когорт 6-ю месяцами и оставив когорты с набором значений за первые 6-ть месяцев
cohort_cnt_o_m_slice = cohort_cnt_o_m.loc[:'2018-01-01', '0':'5']
cohort_cnt_o_m_slice
cohort_cnt_o_m_slice_mean_all = cohort_cnt_o_m_slice.mean(axis=1).round(2)
cohort_cnt_o_m_slice_mean_all
cohort_cnt_o_m_slice_mean_avg = cohort_cnt_o_m_slice_mean_all.mean()
cohort_cnt_o_m_slice_mean_avg
При данной низкой возвращаемости пользователей в сервис говорить о среднем количестве заказов за какой-либо период не корректно, потому что фактически пользователь использует сервис в первый месяц и затем уходит из него и те 5-10% процентов возвращаемости могут обеспечивать одни и теже пользователи - постоянные клиенты (подробнее см. дальше разделы исследования).
cohort_o_gr_m.head(5)
plt.figure(figsize=(12,4), dpi=200)
sns.distplot(cohort_o_gr_m['sum_buy_per_one_user'],kde=False)
plt.title('Гистограмма средней выручки на пользователя')
plt.ylabel('Частотность значения, шт')
plt.xlabel('Выручка, у.е.')
plt.show()
Распредееление значений средней выручки показывает, что фактически у нас присутствует два распределения средней выручки:
Поэтому считать общую среднюю при данном виде распределения - не корректно и такое среднее не отразит фактическую ситуацию в сервисе. В данной ситуации необходимо рассчитать отдельно среднее значение (как mean) для первого месяца когорты (lifetime=0) и медиану для всех других значений lifetime (медиану, поскольку распределение скошено влево)
# среднее значение (mean) в первый месяц когорты за весь период
round(cohort_o_gr_m[cohort_o_gr_m['lifetime'] == 0]['sum_buy_per_one_user'].mean(), 2)
# среднее значение (median) в месяцы когорты (кроме первого) за весь период
round(cohort_o_gr_m[cohort_o_gr_m['lifetime'] != 0]['sum_buy_per_one_user'].median(), 2)
# создадим сводную таблицу для анализа средней выручки с пользователя во времени
cohort_sum_o_m = cohort_o_gr_m.pivot_table(index='first_buy_m', columns='lifetime', values= 'sum_buy_per_one_user').round(2)
cohort_sum_o_m.index = cohort_sum_o_m.index.strftime('%Y-%m-%d')
plt.figure(figsize=(12,4), dpi=200)
sns.heatmap(cohort_sum_o_m, vmax=1.2, annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('Выручка с одного пользователя по когортам во времени')
plt.ylabel('Когорта ')
plt.xlabel('Время жизни когорты, мес.')
plt.show()
df_o.head()
# рассчитаем суммарные значения (общую выручку и количество уникальных клиентов) по каждому месяцу отдельно
mean_buy_month = df_o.groupby('buy_m').agg({'revenue': 'sum', 'uid':'nunique'}).reset_index()
mean_buy_month.columns = ['buy_m','sum_buy', 'count_u_users']
# средняя выручка с пользователя в месяц
mean_buy_month['sum_per_one_user'] = (mean_buy_month['sum_buy'] / mean_buy_month['count_u_users']).round(2)
mean_buy_month
# рассчитаем среднее значение ежемесячной выручки на одного пользователя за все периоды
round(mean_buy_month['sum_per_one_user'].mean(), 2)
print(f"Средняя выручка с пользователя = {round(mean_buy_month['sum_per_one_user'].mean(), 2)} у.е.")
plt.figure(figsize=(12,4), dpi=200)
sns.lineplot(x='buy_m', y='sum_per_one_user', data=mean_buy_month, label='Cред. выручка с польз. в месяц ')
plt.title('График изменения средней выручки с одного пользователя по месяцам')
plt.xlabel('')
plt.ylabel('Средняя выручка с пользователя, у.е.')
plt.xticks(ticks=mean_buy_month['buy_m'], rotation=45)
plt.axhline(round(mean_buy_month['sum_per_one_user'].mean(), 2),
ls='--', c='r', label='Cред. выручка с польз. за весь период')
plt.legend()
plt.show()
cohort_o_gr_m['avg_check'] = cohort_o_gr_m['sum_buy'] / cohort_o_gr_m['count_buy']
avg_check_cohort = cohort_o_gr_m.pivot_table(index='first_buy_m', columns='lifetime', values='avg_check').round(2)
avg_check_cohort.index = avg_check_cohort.index.strftime('%Y-%m-%d')
plt.figure(figsize=(12,4), dpi=200)
sns.heatmap(avg_check_cohort, vmax=20, annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('Средний чек по когортам во времени')
plt.ylabel('Когорта ')
plt.xlabel('Время жизни когорты, мес.')
plt.show()
# используем ранее составленную сводную таблицу cohort_sum_o_m для изучения накопительного LTV
# выручка равна валовой прибыли т.к. маржинальность сервиса 100%
# для расчета накопительного lTV по когорта используем метод cumsum()
cohort_ltv_cum_m = cohort_sum_o_m.cumsum(axis=1)
plt.figure(figsize=(12,4), dpi=200)
sns.heatmap(cohort_ltv_cum_m, annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('Накопительный LTV по когортам во времени')
plt.ylabel('Когорта ')
plt.xlabel('Время жизни когорты, мес.')
plt.show()
# ограничим накопительный LTV данными за полные 6 месяцев когор
cohort_ltv_cum_m = cohort_sum_o_m.cumsum(axis=1)
plt.figure(figsize=(12,4), dpi=200)
sns.heatmap(cohort_ltv_cum_m.loc[:'2017-12-01', '0':'5'], annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('Накопительный LTV по когортам (в периоде 6 месяцев)')
plt.ylabel('Когорта ')
plt.xlabel('Время жизни когорты, мес.')
plt.show()
cohort_ltv_cum_m.loc[:'2017-12-01', '5':'5'].plot(grid=True, figsize=(12,6), legend=False)
plt.title('График изменения накопительного LTV (за 6 месяцев) по когортам')
plt.ylabel('Накопительный LTV, у.е.')
plt.show()
print(f"Общая сумма расходов на маркетинг = {df_c['costs'].sum(): .2f} у.е.")
# добавление столбца с датой в разрезе месяца в формате str и datetime
df_c['y_m_dt'] = df_c['dt'].dt.strftime('%Y-%m-01')
df_c['first_buy_m'] = df_c['dt'].astype('datetime64[M]')
df_c.sample(3)
# построение сводной таблицы с подитогами
mark_gr_s_t = df_c.pivot_table(index='source_id', columns='y_m_dt', values='costs', aggfunc='sum', margins=True)
mark_gr_s_t
mark_gr_s_t.loc[[1, 2, 3, 4, 5, 9, 10], '2017-06-01':'2018-05-01'].T.plot(grid=True, figsize=(16,6))
plt.title('График изменения расходов по источникам рекламы (source_id) во времени')
plt.ylabel('Расходы, у.е.')
plt.xlabel('Месяцы')
plt.show()
mark_gr_s_t.loc['All', '2017-06-01':'2018-05-01'].T.plot(grid=True, figsize=(16,6))
plt.title('График изменения общих расходов на рекламу во времени')
plt.ylabel('Расходы, у.е.')
plt.xlabel('Месяцы')
plt.show()
# справочник количества уникальных посетителей в месяц
ses_cnt_uuser_grby_sour_month = df_v.groupby(['source_id', 'ses_m_dt']).agg({'uid': 'nunique'}).reset_index()
ses_cnt_uuser_grby_sour_month = (ses_cnt_uuser_grby_sour_month
.rename(columns={'ses_m_dt': 'first_buy_m' , 'uid': 'cnt_un_users_ses'}))
ses_cnt_uuser_grby_sour_month.head(10)
# справочник источников и устройств первой сессии для пользователей
first_ses_source_device_by_users = (df_v.loc[(df_v['start_ts'] == df_v['first_ses_ts']),
['uid', 'source_id', 'device']])
first_ses_source_device_by_users
# проверим строки на уникальность по uid
uid_prodlem = first_ses_source_device_by_users['uid'].duplicated()
first_ses_source_device_by_users[uid_prodlem]['uid']
# "изучим глазами", что не так с этим uid
df_v[df_v['uid'] == 1981020429381477763]
У этого пользователя две сессии начинаются одновременно , но одна нулевой длительности, другая 120 секунд device и source_id, совпадают, поэтому одну из этих строк удаляем (для этого используем полученную ранее логическую маску).
Примечание: Необходимо информировать о данном инциденте ответственных сотрудников для принятия мер.
first_ses_source_device_by_users = first_ses_source_device_by_users[~uid_prodlem]
len(first_ses_source_device_by_users)
# добавим в таблицу заказов информацию об источниках и устройстве первой сессии
# для этого соединим таблицы по uid
df_o = df_o.merge(first_ses_source_device_by_users, on='uid')
df_o.head()
# группировка данных для когортного анализа
group_by_source = (df_o.groupby(['source_id', 'first_buy_m', 'lifetime'])
.agg({'revenue': 'sum', 'uid': ['count', 'nunique']})
.reset_index())
group_by_source.columns = ['source_id', 'first_buy_m', 'lifetime', 'sum_buy', 'count_buy', 'count_un_users_buy']
group_by_source.head()
# справочник количества пользователей в когорте, разбитых по источникам
coutn_users_cohort_by_source = (group_by_source[group_by_source['lifetime'] == 0]
[['source_id', 'first_buy_m', 'count_un_users_buy']])
coutn_users_cohort_by_source = coutn_users_cohort_by_source.rename(columns={'count_un_users_buy': 'count_users_in_cohort'})
coutn_users_cohort_by_source.head()
group_by_source = group_by_source.merge(coutn_users_cohort_by_source, on=['source_id', 'first_buy_m'])
group_by_source.head()
# добавим количество уникальных сессий в когорты
group_by_source = group_by_source.merge(ses_cnt_uuser_grby_sour_month,on=['source_id', 'first_buy_m'])
group_by_source.head()
# справочник расходов по месяцам и по источникам
cost_sum_grby_sour_month = df_c.groupby(['source_id', 'first_buy_m']).agg({'costs': 'sum'}).reset_index()
cost_sum_grby_sour_month.head()
# добавим расходы по источникам и по месяцам в таблицу group_by_source
group_by_source = group_by_source.merge(cost_sum_grby_sour_month, on=['source_id', 'first_buy_m'])
group_by_source.head()
# расчитаем CAC на покупателя в когорте и на пользователя сессий
group_by_source['cac_buy'] = (group_by_source['costs'] / group_by_source['count_users_in_cohort']).round(2)
group_by_source['cac_ses'] = (group_by_source['costs'] / group_by_source['cnt_un_users_ses']).round(2)
group_by_source['first_buy_m'] = group_by_source['first_buy_m'].dt.strftime('%Y-%m-%d')
group_by_source.head()
group_by_source_cac = (group_by_source[group_by_source['lifetime'] == 0]
[['source_id', 'first_buy_m', 'cac_buy', 'cac_ses']])
group_by_source_cac
# создадим сводную таблицу для наглядного отображения данных отдельно по каждому CAC (покупатели и посетители)
# ПОКУПАТЕЛИ
report_cac_buy = group_by_source_cac.pivot_table(index='source_id', columns='first_buy_m', values='cac_buy')
report_cac_buy['avg'] = round(report_cac_buy.mean(axis=1), 2)
report_cac_buy
report_cac_buy.loc[:, '2017-06-01':'2018-05-01'].T.plot(grid=True, figsize=(16,6))
plt.title('График изменения расходов на одного покупателя по источникам рекламы во времени')
plt.ylabel('Расходы, у.е.')
plt.xlabel('Месяцы')
plt.show()
report_cac_buy.loc[:, 'avg'].sort_values(ascending=False).plot(grid=True, kind='bar', figsize=(16,6))
plt.title('Средние расходы на одного покупателя по источникам рекламы')
plt.ylabel('Расходы, у.е.')
plt.xlabel('Источники рекламы')
plt.xticks(rotation=1)
plt.show()
# ПОСЕТИТЕЛИ
report_cac_ses = group_by_source_cac.pivot_table(index='source_id', columns='first_buy_m', values='cac_ses')
report_cac_ses['avg'] = round(report_cac_ses.mean(axis=1), 2)
report_cac_ses
report_cac_ses.loc[:, '2017-06-01':'2018-05-01'].T.plot(grid=True, figsize=(16,6))
plt.title('График изменения расходов на одного посетителя по источникам рекламы во времени')
plt.ylabel('Расходы, у.е.')
plt.xlabel('Месяцы')
plt.show()
report_cac_ses.loc[:, 'avg'].sort_values(ascending=False).plot(grid=True, kind='bar', figsize=(16,6))
plt.title('Средние расходы на одного посетителя по источникам рекламы')
plt.ylabel('Расходы, у.е.')
plt.xlabel('Источники рекламы')
plt.xticks(rotation=1)
plt.show()
Источники рекламы имеют разные расходы и приносят сервису разное количество покупателей и средние расходы на одного покупателя имею разные значения.
Наибольшая стоимость привлечения у источника 3 (у этого источника самые больше абсолютные расходы).
Нименьшая стоимость привлечения у источника 10 (при этом у него самые низкие абсолютные расходы).
Если рассматривать расходы в пересчете на посетители то в отношении источника 3 картина не меняется, а наименьшие расходы у источника 9 (это свидетельствует, что из для источника 9 конверсия в покупку хуже).
group_by_source.head()
# расчет LTV ROMI и добавление столбца в таблицу
group_by_source['ltv'] = group_by_source['sum_buy'] / group_by_source['count_users_in_cohort']
group_by_source['romi'] = group_by_source['ltv'] / group_by_source['cac_buy']
group_by_source
report_romi = (group_by_source.pivot_table(index=['source_id', 'first_buy_m'], columns='lifetime', values='romi')
.cumsum(axis=1).round(2))
report_romi.head(15)
source_num = report_romi.index.get_level_values(0).unique()
# определение функции для построения когортного анализа в разрезе источников
def cohort_plot_source(num):
plt.figure(figsize=(12,4), dpi=200)
sns.heatmap(report_romi.loc[num], annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('ROMI по когортам для источника %s' % num)
plt.ylabel('Когорта ')
plt.xlabel('Время жизни когорты, мес.')
plt.show()
for num in source_num:
cohort_plot_source(num)
# проверка данных по источнику 10
data_source_ten = group_by_source[group_by_source['source_id'] == 10][['first_buy_m', 'lifetime', 'sum_buy', 'count_buy']]
gr_source_ten_sum = data_source_ten.pivot_table(index='first_buy_m', columns='lifetime', values='sum_buy')
plt.figure(figsize=(10,3), dpi=100)
sns.heatmap(gr_source_ten_sum, annot=True, fmt='.2f', linewidths=1, linecolor='gray')
plt.title('Сумма продаж по когортам для источника 10')
plt.ylabel('Когорта ')
plt.xlabel('Время жизни когорты, мес.')
plt.show()
# определим функция для вывода средней окупаемости (ROMI) для каждого источника за 6 месяцев
range_month=['2017-06-01', '2017-07-01','2017-08-01', '2017-09-01', '2017-10-01', '2017-11-01', '2017-12-01']
def source_romi_six_month(num):
plt.figure(figsize=(0.5, 0.5), dpi=100)
sns.heatmap(report_romi.loc[(num, range_month), 5]
.to_frame().mean().to_frame(), cbar=False, cmap='hsv', annot=True, fmt='.2f')
plt.title('Средняя окупаемость (ROMI) для источника %s за 6 месяцев' % num)
plt.xticks(ticks=[], lable='')
plt.yticks(ticks=[], lable='')
plt.show()
for num in source_num:
source_romi_six_month(num)
group_by_device_source = (df_o[df_o['lifetime'] == 0].groupby(['source_id', 'device', 'first_buy_m'])
.agg({'uid': 'nunique'}))
group_by_device_source = group_by_device_source.rename(columns={'uid': 'count_users_in_cohort'})
group_by_device_source.head()
# определим функцию для вывода графиков количества пользователей когорт по источникам и платформам
def plot_desktop_touch_users(num):
ax=group_by_device_source.loc[(num, 'desktop'), :].plot(grid=True, figsize=(12,6))
group_by_device_source.loc[(1, 'touch'), :].plot(grid=True, ax=ax)
plt.legend(('desktop', 'touch'))
plt.xlabel('')
plt.ylabel('Количество покупателей, шт.')
plt.title('Количество покупателей в когортах по платформам для источника %s' % num)
plt.show()
for num in source_num:
plot_desktop_touch_users(num)